from django.shortcuts import render
from django.db.models import *
from typing import List, Union
from django.db import connection
from django.db import connections
from datetime import datetime

from .models import Down, Repair, Parse, Down1, Parse1, DownParse, ParseDown


# /down
# 返回主页面
def down1(request):
    # 拿到最新记录的时间
    start_time, stop_time = initial_time()

    cmy1, cmy2, cmy3 = make_content(start_time, stop_time)
    time = [start_time, stop_time]
    context = {'time': time, 'cmy1': cmy1, 'cmy2': cmy2, 'cmy3': cmy3}
    print(cmy3)
    return render(request, 'production_ms/down/template-down.html', context)


# /down/ajax(post)
def down2(request):
    start_time = request.POST.get('start')
    stop_time = request.POST.get('stop')
    cmy1, cmy2, cmy3 = make_content(start_time, stop_time)
    time = [start_time, stop_time]
    context = {'time': time, 'cmy1': cmy1, 'cmy2': cmy2, 'cmy3': cmy3}

    return render(request, 'production_ms/down/down.html', context)


def make_content(start_time: str, stop_time: str):
    cmy_r = f"date >= '{start_time}' and date <= '{stop_time}'"

    # 数据区域1：表格 开始---------------------------------------------------------------------
    # 数据表格区域，顺序是机组，维修，换辊，待料
    # cmy1 = [[机组，维修，换辊，待料],[机组，维修，换辊，待料]...]
    sql1 = f'''
            select unit,sum(machine),sum(electric),sum(self),sum(change),sum(stop),sum(work),sum(product)
            from down_parse  
            where {cmy_r}
            GROUP BY unit
            order by sum(work) desc 
        '''
    with connections['production'].cursor() as cur:
        cur.execute(sql1)
        cmy1 = cur.fetchall()  # 数据区域1的总数组
        # print(cmy1)
        # for i in data1:
        #
        #     cmy1.append([i[0], i[1], i[2], i[3], i[4], i[5], i[6], i[7]])

        # 数据区域2 重写
        # 坐标轴的机组和时间数据
        # crew = ['761', '762'...]
        # time = ['2021-06-01' ,'2021-06-02'...]
        # [机组,时间,[故障名,[机组名,[数据]],[机组名,[数据]]]...]]
        # 柱状图的种类 ['761', '762'...]
        cur.execute(f'''select DISTINCT unit from down1 where {cmy_r}''')
        # data2_crew = cur.fetchall()
        data2_crew = list(map(lambda i: i[0], cur.fetchall()))
        # print(data2_crew)
        # 图表横坐标 ['2021-06-01' ,'2021-06-02'...]
        cur.execute(f'''select DISTINCT to_char(date, 'YYYY-MM-DD') from down1 where {cmy_r}''')
        # data2_time = cur.fetchall()
        data2_time = list(map(lambda i: i[0], cur.fetchall()))
        # print(data2_time)
    # 显示图表名称的数组
    picture_name = ['机修时间', '电修时间', '自修时间', '换辊时间', '待料时间', '开机时间']

    # 用于轮询搜索数据的数组
    col = ['machine_day', 'electric_day', 'self_day', 'change_day', 'stop_day', 'work_day']

    cmy2 = [data2_crew, data2_time]
    for i in picture_name:
        cmy2.append([i])

    # temp1 = [故障名,[机组名,[数据]],[机组名,[数据]]...]
    num = 0
    with connections['production'].cursor() as cur:
        for i in cmy2[2:]:
            for j in data2_crew:
                cur.execute(f'''
                    select {col[num]} from down1 where {cmy_r} and unit = '{j}'
                ''')
                data1 = list(map(lambda i: float(i[0]), cur.fetchall()))
                # data1 = cur.fetchall()
                i.append([j, data1])
            num = num + 1
    # 数据区域2：折线图 结束---------------------------------------------------------------------

    # 数据区域3：柱状图和环形饼图 开始---------------------------------------------------------------------
    # 数组内容：[[机组，停车天数，机修，电修，自修，换辊，待料，运行天数]...]
    cmy3 = [['机组', '停车天数', '机修', '电修', '自修', '换辊', '待料', '运行天数']]
    with connections['production'].cursor() as cur:
        cur.execute(
            f'''
                    select unit,sum(1.0-work_day),sum(machine_day),sum(electric_day),sum(self_day),sum(change_day),sum(self_day),sum(work_day)
                    from down1
                    where date >= '{start_time}' and date <= '{stop_time}'
                    GROUP BY unit 
                '''
        )
        data3 = cur.fetchall()
    for i in data3:
        cmy3.append([i[0], float(i[1]), float(i[2]), float(i[3]), float(i[4]), float(i[5]), float(i[6]), float(i[7])])

    return cmy1, cmy2, cmy3


# 返回未选择日期时的初始时间
def initial_time():
    a = Parse.objects.values_list('date').latest('date')[0].strftime('%Y-%m-%d')
    return a, a
